﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ParkingApp.Common.Entities;

namespace ParkingApp.DataService
{
    public class SqliteDataAccess : IDataAccess, IUserDataAccess
    {
        string _connectionString = @"Data Source=C:\my-parking-metro-app\ParkingAppDB\ParkingAppDB.s3db";
        public SqliteDataAccess(string connectionString) {
            _connectionString = connectionString;
        }
        public List<User> GetUsers()
        {
            List<User> returnList = null;
            using (var conn = new SQLiteConnection(_connectionString))
            {
                conn.Open();
                using (var cmd = new SQLiteCommand(@"SELECT 
                                                        UserId
                                                        ,Username
                                                        ,Password
                                                        ,FirstName
                                                        ,LastName
                                                        ,DateAdded
                                                        ,AddedBy
                                                    FROM User"
                    , conn)) {
                        using (var reader = cmd.ExecuteReader()) {
                            while (reader.Read()) {
                                if (returnList == null) returnList = new List<User>();
                                returnList.Add(new User() { 
                                    UserId = (long)reader["UserId"],
                                    FirstName = (string)reader["FirstName"],
                                    LastName = (string)reader["LastName"],
                                    Username = (string)reader["Username"],
                                    Password = (string)reader["Password"],
                                    DateAdded = (DateTime)reader["DateAdded"],
                                    AddedBy = (long)reader["AddedBy"]
                                });
                            }
                        }
                }
                conn.Close();
            }
            return returnList;
        }

        public User GetUser(long userId)
        {
            User returnObject = null;
            using (var conn = new SQLiteConnection(_connectionString))
            {
                using (var cmd = new SQLiteCommand(@"SELECT 
                                                        UserId
                                                        ,Username
                                                        ,Password
                                                        ,FirstName
                                                        ,LastName
                                                        ,DateAdded
                                                        ,AddedBy
                                                FROM User
                                                WHERE UserId = @UserId"
                    , conn))
                {
                    cmd.Parameters.Add(new SQLiteParameter("@UserId", userId));
                    conn.Open();
                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            returnObject = new User()
                            {
                                UserId = (long)reader["UserId"],
                                FirstName = (string)reader["FirstName"],
                                LastName = (string)reader["LastName"],
                                Username = (string)reader["Username"],
                                Password = (string)reader["Password"],
                                DateAdded = (DateTime)reader["DateAdded"],
                                AddedBy = (long)reader["AddedBy"]
                            };
                        }
                    }
                    conn.Close();
                }
            }
            return returnObject;
        }

        public User SaveUser(User user)
        {
            string sql = "";
            if (user.UserId == 0) { // insert
                sql = @"INSERT INTO User (
                            Username
                            ,Password
                            ,FirstName
                            ,LastName
                            ,DateAdded
                            ,AddedBy)
                        SELECT 
                                @Username
                                ,@Password
                                ,@FirstName
                                ,@LastName
                                ,@DateAdded
                                ,@AddedBy;
                        SELECT last_insert_rowid();
                            ";
            }
            else { // update
                sql = @"UPDATE User
                        SET
                            Username    = @Username
                            ,Password   = @Password
                            ,FirstName  = @FirstName
                            ,LastName   = @LastName
                            ,DateAdded  = @DateAdded
                            ,AddedBy    = @AddedBy
                        WHERE
                            UserId      = @UserId;
                        SELECT @UserId";
            }
            using (var conn = new SQLiteConnection(_connectionString)) {
                using (var cmd = new SQLiteCommand(sql, conn)) {
                    conn.Open();
                    cmd.Parameters.AddWithValue("@UserId", user.UserId);
                    cmd.Parameters.AddWithValue("@Username", user.Username);
                    cmd.Parameters.AddWithValue("@Password", user.Password);
                    cmd.Parameters.AddWithValue("@FirstName", user.FirstName);
                    cmd.Parameters.AddWithValue("@LastName", user.LastName);
                    cmd.Parameters.AddWithValue("@DateAdded", user.DateAdded);
                    cmd.Parameters.AddWithValue("@AddedBy", user.AddedBy);
                    long retVal = (long)cmd.ExecuteScalar();
                    conn.Close();
                    user.UserId = retVal;
                }
            }
            return user;
        }

        public void DeleteUser(User user)
        {
            string sql = "DELETE FROM User WHERE UserId = @UserId";
            using (var conn = new SQLiteConnection(_connectionString))
            {
                using (var cmd = new SQLiteCommand(sql, conn))
                {
                    conn.Open();
                    cmd.Parameters.AddWithValue("@UserId", user.UserId);
                    cmd.ExecuteNonQuery();
                    user = null;
                    conn.Close();
                }
            }
        }

        public User ValidPassword(string username, string password)
        {
            User user = null;
            string sql = @"SELECT
                            UserId 
                            ,Username
                            ,Password
                            ,FirstName
                            ,LastName
                            ,DateAdded
                            ,AddedBy
                        FROM
                            User
                        WHERE
                            Username = @Username
                        AND
                            Password = @Password;";
            using(var conn = new SQLiteConnection(_connectionString)) {
                using(var cmd = new SQLiteCommand(sql,conn)) {
                    conn.Open();
                    cmd.Parameters.AddWithValue("@Username", username);
                    cmd.Parameters.AddWithValue("@Password", password);
                    using(var reader = cmd.ExecuteReader()) {
                        while(reader.Read()) {
                            user = new User() {
                                UserId = (long)reader["UserId"],
                                FirstName = (string)reader["FirstName"],
                                LastName = (string)reader["LastName"],
                                Username = (string)reader["Username"],
                                Password = (string)reader["Password"],
                                DateAdded = (DateTime)reader["DateAdded"],
                                AddedBy = (long)reader["AddedBy"]
                            };
                        }
                    }
                    conn.Close();
                }
            }
            if(user == null) throw new ApplicationException("user not found/ username password incorrect.");
            return user;
        }
    }
}
